﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using YXShop.IDAL.Templates;
using System.Data.SqlClient;
using System.Data;

namespace YXShop.SQLServerDAL.Templates
{
    public class Label:ILabel
    {
        #region "DataBase Operation"
        /// <summary>
        /// 在数据库中新增一个持久化对象,自增长列UID的值会自动从数据库中返回
        /// </summary>
        /// <remarks></remarks>
        public int Create(YXShop.Model.Templates.Label model)
        {
            string sequel = "Insert into [yxs_label](";
            sequel = sequel + "[moduleid],[tid], [name], [content], [description], [createtime], [labelclass],[isstorelabel])";
            sequel = sequel + "Values(";
            sequel = sequel + "@moduleid, @tid,@name,@content,@description,@createtime,@labelclass,@isstorelabel) Select scope_IDENTITY() ";
            SqlParameter[] paras = (SqlParameter[])this.ValueParas(model);
            object obj = ChangeHope.DataBase.SQLServerHelper.ExecuteSql(sequel, paras);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 删除数据库中指定的持久化对象的数据, 并提供事务支持
        /// </summary>
        /// <remarks></remarks>
        public int Delete(int id)
        {
            string sequel = string.Empty;
            sequel = "Delete From [yxs_label]" + this.UpdateWhereSequel;
            SqlParameter[] paras = (SqlParameter[])this.UpdateParas(id);
            object obj = ChangeHope.DataBase.SQLServerHelper.ExecuteSql(sequel, paras);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 将一个持久化对象的修改保存到数据库, 并提供事务支持
        /// </summary>
        /// <remarks></remarks>
        public int Amend(YXShop.Model.Templates.Label model)
        {
            string sequel = "Update [yxs_label] set  ";
            sequel = sequel + "[moduleid] =@moduleid ,[tid] =@tid ,[name]=@name ,[content] =@content ,[description] =@description ,[createtime] =@createtime,[labelclass]=@labelclass,[isstorelabel]=@isstorelabel";
            sequel = sequel + UpdateWhereSequel;
            object obj = ChangeHope.DataBase.SQLServerHelper.ExecuteSql(sequel, (SqlParameter[])this.ValueParas(model));
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        #endregion

        #region "Data Load"

        /// <summary>
        /// 返回数据
        /// </summary>
        /// <param name="row"></param>
        /// <returns></returns>
        public YXShop.Model.Templates.Label GetModel(System.Data.DataRow row)
        {
            YXShop.Model.Templates.Label model = new YXShop.Model.Templates.Label();
            if (row != null)
            {
                model.ID = int.Parse(row["id"].ToString());
                model.Moduleid = int.Parse(row["moduleid"].ToString());
                model.TID = int.Parse(row["tid"].ToString());
                model.Name = row["name"].ToString();
                model.Description = row["description"].ToString();
                model.Content = row["content"].ToString();
                model.CreateTime = DateTime.Parse(row["createtime"].ToString());
                model.LabelClass = int.Parse(row["labelclass"].ToString());
                model.IsStoreLabel = int.Parse(row["isstorelabel"].ToString());
                return model;
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// ID查询
        /// </summary>
        /// <remarks></remarks>
        public YXShop.Model.Templates.Label GetModelID(int id)
        {
            string sequel = SelectSequel + UpdateWhereSequel;
            SqlParameter[] paras = (SqlParameter[])this.UpdateParas(id);
            DataSet ds = ChangeHope.DataBase.SQLServerHelper.Query(sequel, paras);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return GetModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// 名称查询
        /// </summary>
        /// <remarks></remarks>
        public YXShop.Model.Templates.Label GetModelName(string name)
        {
            string sequel = SelectSequel + " where name=@name";
            SqlParameter[] paras = new SqlParameter[1];
            paras[0] = new SqlParameter("@name", SqlDbType.VarChar, 50);
            paras[0].Value = name;
            DataSet ds = ChangeHope.DataBase.SQLServerHelper.Query(sequel, paras);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return GetModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// 查询所有数据
        /// </summary>
        /// <remarks></remarks>
        public ChangeHope.DataBase.DataByPage GetList()
        {
            ChangeHope.DataBase.DataByPage dataPage = new ChangeHope.DataBase.DataByPage();
            dataPage.Sql = "[select] * [from] yxs_label [where] 1=1 [order by] id desc";
            dataPage.GetRecordSetByPage();
            return dataPage;
        }

        /// <summary>
        /// 查询所有数据
        /// </summary>
        /// <remarks></remarks>
        public ChangeHope.DataBase.DataByPage GetList(string tid)
        {

            ChangeHope.DataBase.DataByPage dataPage = new ChangeHope.DataBase.DataByPage();
            dataPage.Sql = "[select] * [from] yxs_label [where] 1=1 and tid="+tid+" [order by] id desc";
            dataPage.GetRecordSetByPage();
            return dataPage;
        }
        public DataTable GetPartData(string tid)
        {
            string strSql = "Select * From yxs_label where tid=" + tid + "";
            DataTable dt = ChangeHope.DataBase.SQLServerHelper.Query(strSql.ToString()).Tables[0];
            return dt;
        }
        /// <summary>
        /// 统计每类标签个数
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int GetCount(int id)
        {
            string sql = "select count(*) from yxs_label where tid='" + id + "'";
            return ChangeHope.Common.StringHelper.StringToInt(ChangeHope.DataBase.SQLServerHelper.GetSingle(sql).ToString());
        }
        #endregion

        #region "Other function"
        string selectSequel = string.Empty;
        /// <summary>
        /// 该数据访问对象从数据库中提取数据的Sql语句 
        /// </summary>
        /// <remarks></remarks>
        protected string SelectSequel
        {
            get
            {
                if (selectSequel == string.Empty)
                    selectSequel = "Select [id], [moduleid],[tid], [name], [content], [description], [createtime], [labelclass],[isstorelabel],1 PersistStatus  From [yxs_label] ";
                return selectSequel;
            }
            set
            {
                this.selectSequel = value;
            }
        }
        protected string UpdateWhereSequel
        {
            get
            {
                return " Where [id] = @id";
            }
        }
        /// <summary>
        /// 该数据访问对象的属性值装载到数据库更新参数数组
        /// </summary>
        /// <remarks></remarks>
        public IDbDataParameter[] ValueParas(YXShop.Model.Templates.Label model)
        {
            SqlParameter[] paras = new SqlParameter[9];
            paras[0] = new SqlParameter("@id", model.ID);
            paras[1] = new SqlParameter("@moduleid", SqlDbType.Int, 4);
            paras[2] = new SqlParameter("@tid", SqlDbType.Int,4);
            paras[3] = new SqlParameter("@name", SqlDbType.VarChar, 50);
            paras[4] = new SqlParameter("@content", SqlDbType.Text);
            paras[5] = new SqlParameter("@description", SqlDbType.VarChar, 200);
            paras[6] = new SqlParameter("@createtime", SqlDbType.DateTime, 8);
            paras[7] = new SqlParameter("@labelclass", SqlDbType.Int, 4);
            paras[8] = new SqlParameter("@isstorelabel",SqlDbType.Int,4);

            paras[0].DbType = DbType.Int32;
            paras[1].Value = model.Moduleid;
            paras[2].Value = model.TID;
            paras[3].Value = model.Name;
            paras[4].Value = model.Content;
            paras[5].Value = model.Description;
            paras[6].Value = model.CreateTime;
            paras[7].Value = model.LabelClass;
            paras[8].Value = model.IsStoreLabel;
            return paras;
        }

        public IDbDataParameter[] UpdateParas(int id)
        {
            SqlParameter[] paras = new SqlParameter[1];
            paras[0] = new SqlParameter("@id", (object)id);
            paras[0].DbType = DbType.Int32;
            return paras;
        }

        #endregion
    }
}
